﻿using System;
using System.Data;
using System.Text;
using System.Globalization;

using BizElements.Core.ProviderComponents;

namespace BizElements.Core.Oracle
{
    sealed class OracleInserter : IInserter
    {
        #region RenderInsert.

        public DbParameter RenderInsert(InsertStatement insert, DbParameter nextSequence, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
        {
            // Auto ID: sequence if it is defined, or NULL and hope that a trigger will take care of auto ID field.
            IDbColumn autoIdField = GetAutoIdField(insert.Table);
            bool hasAutoIdField = (autoIdField != null);
            bool autoIdIsAlreadyInInsertList = IsAutoIdFieldInColumnValueList(insert);
            bool mustAppendAutoIdField = (hasAutoIdField && !autoIdIsAlreadyInInsertList);

            AppendInsertIntoTableName(insert, dbms, output);
            AppendColumnListInBrackets(insert, dbms, output, autoIdField, mustAppendAutoIdField);
            RenderValueListAndNextSequenceExpression(insert, dbms, output, parameters, autoIdField, mustAppendAutoIdField);

            if (autoIdField != null && nextSequence != null)
            {
                // RETURNING id
                output.Append(" RETURNING ");
                autoIdField.RenderColumnName(dbms, output);
                output.Append(" INTO ");
                nextSequence.Render(dbms, output, parameters);
            }

            return nextSequence;
        }          

        private static IDbColumn GetAutoIdField(IDbTable table)
        {
            IDbColumn identityField = null;
            foreach (IDbColumn field in table.Columns)
            {
                if (field.AutoIncrement)
                {
                    identityField = field;
                    break;
                }
            }

            return identityField;
        }

        private static bool IsAutoIdFieldInColumnValueList(InsertStatement insert)
        {
            foreach (InsertExpression colValPair in insert.ColumnsAndValues)
            {
                if (colValPair.Column.AutoIncrement)
                    return true;
            }

            return false;
        }

        private static void AppendInsertIntoTableName(InsertStatement insert, DbmsType dbms, StringBuilder output)
        {
            output.Append("INSERT INTO ");
            insert.Table.RenderTableName(dbms, output);
        }

        private static void AppendColumnListInBrackets(InsertStatement insert, DbmsType dbms, StringBuilder output, IDbColumn autoIdField, bool mustAppendAutoIdField)
        {
            output.Append(" (");
            foreach (InsertExpression colValPair in insert.ColumnsAndValues)
            {
                colValPair.Column.RenderColumnName(dbms, output);
                output.Append(", ");
            }

            if (mustAppendAutoIdField)
            {
                autoIdField.RenderColumnName(dbms, output);
                output.Append(", ");
            }

            // Remove last ", " string.
            output.Remove(output.Length - 2, 2);
            output.Append(")");
        }

        private static void RenderValueListAndNextSequenceExpression(InsertStatement insert, DbmsType dbms, StringBuilder output, DbParameterCollection parameters, IDbColumn autoIdField, bool mustAppendAutoIdField)
        {
            output.Append(" VALUES (");

            foreach (InsertExpression colValPair in insert.ColumnsAndValues)
            {
                IDbColumn currColumn = colValPair.Column;
                if (currColumn.AutoIncrement)
                {
                    AppendAutoIdFieldToValueList(output, currColumn);
                }
                else
                {
                    IRenderSql itemToInsert = colValPair.ValueExpression;
                    itemToInsert.Render(dbms, output, parameters);
                }

                output.Append(", ");
            }

            if (mustAppendAutoIdField)
            {
                AppendAutoIdFieldToValueList(output, autoIdField);
                output.Append(", ");
            }

            // Remove last ", " string.
            output.Remove(output.Length - 2, 2);
            output.Append(")");
        }

        private static void AppendAutoIdFieldToValueList(StringBuilder output, IDbColumn autoIdField)
        {
            if (string.IsNullOrEmpty(autoIdField.SequenceName))
            {
                output.Append("NULL");
            }
            else
            {
                output.Append(autoIdField.SequenceName);
                output.Append(".nextval");
            }
        }      

        #endregion

        #region Execute.

        /// <summary>Executes the INSERT command. Automatically generates the code that retrieves the new identity for 
        /// the supported databases. DBMS specific code depends on the DBMS property of the used ConnectionProvider.</summary>
        /// <param name="insert">INSERT statement to execute.</param>
        /// <param name="dbms">Target DBMS.</param>
        /// <param name="conn">Connection-transaction context to use.</param>
        /// <param name="lastExecutedCommandInfo">Output parameter: statistic for executed command.</param>
        /// <returns>Automatically generated ID for inserted row, or <b>null</b> if ID is not automatically generated.</returns>
        public object Execute(InsertStatement insert, DbmsType dbms, IConnectionProvider conn, out CommandExecutionStatistics lastExecutedCommandInfo)
        {
            // Renderer and DBMS will compute next ID, insert row and retrieve ID in one trip.
            StringBuilder cmdtxt = new StringBuilder();
            DbParameterCollection parameters = new DbParameterCollection();
            IDbColumn autoIdField = GetAutoIdField(insert.Table);
            DbParameter newId = (autoIdField != null) ? new DbParameter("newAutoIdentity___", autoIdField.DbType) { Direction = ParameterDirection.Output } : null;
            RenderInsert(insert, newId, dbms, cmdtxt, parameters);

            string command = cmdtxt.ToString();
            object id = null;
            lastExecutedCommandInfo = new CommandExecutionStatistics(command);
            DbUtil.ExecuteNonQuery(conn, command, parameters, CommandType.Text);
            if (newId != null)
            {
                if (newId.Value == DBNull.Value)
                    id = null;
                else
                    id = newId.Value;
            }

            lastExecutedCommandInfo.StopTime();
            return id;
        }

        private static bool HasAutoIdField(IDbTable table)
        {
            foreach (IDbColumn field in table.Columns)
            {
                if (field.AutoIncrement)
                    return true;
            }

            return false;
        }

        #endregion
    }
}